from django.db import models
from django.db import connection



class PartCountManager(models.Manager):
    def __init__(self):
        super(PartCountManager, self).__init__()


    def with_counts(self, category):
        cursor = connection.cursor()
        cursor.execute("""
            SELECT p.id, p.name, u.name, sum(r.quantity)
            FROM storage_part p, storage_unit u, storage_regpart r
            WHERE r.part_id=p.id
            AND p.unit_id=u.id
            AND p.category_id=%s
            GROUP BY r.part_id
            ORDER BY p.name""" % category)
            
        result_list = []
        for row in cursor.fetchall():            
            p = self.model(id=row[0], name=row[1], unit=row[2])
            p.quantity = row[3]
            result_list.append(p)
        return result_list

    
    def part_quantity(self, part_id):        
        cursor = connection.cursor()
        cursor.execute("""
            SELECT r.part_id, sum(r.quantity)
            FROM storage_regpart r, storage_part p
            WHERE r.part_id=p.id
            AND r.part_id=%s
            GROUP BY r.part_id""" % part_id)
        result_dict = {}
        for row in cursor.fetchall():            
            result_dict={'part_quantity':row[1],'part_id':row[0],}
        return result_dict